﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;

namespace BookStore.Controllers
{
    public class BillController : Controller
    {       
        
        private static BillController _controller = null;
        public static BillController getController()
        {
            if (_controller == null)
                return new BillController();
            else
                return _controller;
        }
        public BillController()
        {
            _controller = this;
        }


        internal int createBill(int staffIDString, string sellingdateString)
        {
            int ID = -1;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_CreateBill", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = staffIDString;
                cmd.Parameters.Add("@SellingDate", SqlDbType.Date).Value = sellingdateString;
                SqlParameter returnValue = new SqlParameter("@lastID", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(returnValue);
                cmd.ExecuteNonQuery();
                ID = (int)returnValue.Value;                
            }
            catch (Exception e)
            {
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return ID;
        }

        internal DataTable getBillDetailFromBill(String BillID)
        {
            DataTable dt = new DataTable("GetBillDetailFromBill");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetBillDetailFromBill",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@BillID", SqlDbType.Int).Value = BillID;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        internal DataTable getBillInDateData(DateTime datefrom, DateTime dateto)
        {
            DataTable dt = new DataTable("getBillInDateData");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetBillInDate",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@DateFrom", SqlDbType.Date).Value = datefrom;
                cmd.Parameters.Add("@DateTo", SqlDbType.Date).Value = dateto;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
               // throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckBillInDate(String sellingDate)
        {
            DataTable dt = new DataTable("CheckBillInDate");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_CheckBillInDate", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@SellingDate", SqlDbType.Date).Value = sellingDate;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public void InsertTotalOfBill(String billID, String totalofbill)
        {
            String sql = @"Update BILL Set TotalOfBill = " + totalofbill + "Where BillID = " + billID;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand(sql, con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable getStatisticBillMonth(int month,int year)
        {
            DataTable dt = new DataTable("GetStatisticBill");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetStatisticBillMonth", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;
                cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable getStatisticBillYear(int year)
        {
            DataTable dt = new DataTable("GetStatisticBillYear");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetStatisticBillYear", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Decimal getTotalOfBillInDate(DataGridView dgv)
        {
            Decimal totalofbill = 0;
            if (dgv.Rows.Count != 0)
            {
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    Decimal bill = (Decimal)dgv.Rows[i].Cells[2].Value;
                    totalofbill += bill;
                }
            }
            return totalofbill;
        }

        public String getNumOfBill(DataGridView dgv)
        {
            if (dgv.Rows.Count != 0)
                return dgv.Rows.Count.ToString();
            else
                return "0";
        }

        public DataSet ExportToCrystalReport(String BillID)
        {
            DataSet ds = new DataSet();
            con = BSCommon.getConnection();
            String sql = "SELECT * FROM BILL WHERE BILLID =" + BillID;
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql,con);
                da.Fill(ds, "BILL");
                sql = "SELECT * FROM STAFF WHERE STAFFID =" + ds.Tables["BILL"].Rows[0]["STAFFID"].ToString();
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "STAFF");
                sql = "SELECT * FROM BILLDETAIL WHERE BILLID =" + BillID;
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "BILLDETAIL");
                sql = "SELECT * FROM PRODUCT";
                da.SelectCommand.CommandText = sql;
                da.Fill(ds, "PRODUCT");
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return ds;
        }
    }
}
